using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;


namespace ASPNETVN.PORTAL.Modules.CMS.Components
{
    public partial class Item
    {
        #region Private members.

        protected int _ID;
        protected int _CategoryID;
        protected int _GroupID;
        protected string _Title = string.Empty;
        protected string _SubTitle = string.Empty;
        protected string _Body = string.Empty;
        protected string _ImagePath = string.Empty;
        protected string _Authors = string.Empty;
        protected byte _Rate;
        protected string _Status = string.Empty;
        protected string _DisplayType = string.Empty;
        protected int _TotalViews;
        protected int _TotalComments;
        protected string _Keywords = string.Empty;
        protected string _CreatedBy = string.Empty;
        protected DateTime _CreatedDate = new DateTime(1753, 1, 1);
        protected string _ApprovedBy = string.Empty;
        protected DateTime _ApprovedDate = new DateTime(1753, 1, 1);
        protected DateTime _PublishedDate = new DateTime(1753, 1, 1);
        protected DateTime _ExpiredDate = new DateTime(1753, 1, 1);

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Properties.

        public int ID
        {
            set { this._ID = value; }
            get { return this._ID; }
        }

        public int CategoryID
        {
            set { this._CategoryID = value; }
            get { return this._CategoryID; }
        }

        public int GroupID
        {
            set { this._GroupID = value; }
            get { return this._GroupID; }
        }

        public string Title
        {
            set { this._Title = value; }
            get { return this._Title; }
        }

        public string SubTitle
        {
            set { this._SubTitle = value; }
            get { return this._SubTitle; }
        }

        public string Body
        {
            set { this._Body = value; }
            get { return this._Body; }
        }

        public string ImagePath
        {
            set { this._ImagePath = value; }
            get { return this._ImagePath; }
        }

        public string Authors
        {
            set { this._Authors = value; }
            get { return this._Authors; }
        }

        public byte Rate
        {
            set { this._Rate = value; }
            get { return this._Rate; }
        }

        public string Status
        {
            set { this._Status = value; }
            get { return this._Status; }
        }

        public string DisplayType
        {
            set { this._DisplayType = value; }
            get { return this._DisplayType; }
        }

        public int TotalViews
        {
            set { this._TotalViews = value; }
            get { return this._TotalViews; }
        }

        public int TotalComments
        {
            set { this._TotalComments = value; }
            get { return this._TotalComments; }
        }

        public string Keywords
        {
            set { this._Keywords = value; }
            get { return this._Keywords; }
        }

        public string CreatedBy
        {
            set { this._CreatedBy = value; }
            get { return this._CreatedBy; }
        }

        public DateTime CreatedDate
        {
            set { this._CreatedDate = value; }
            get { return this._CreatedDate; }
        }

        public string ApprovedBy
        {
            set { this._ApprovedBy = value; }
            get { return this._ApprovedBy; }
        }

        public DateTime ApprovedDate
        {
            set { this._ApprovedDate = value; }
            get { return this._ApprovedDate; }
        }

        public DateTime PublishedDate
        {
            set { this._PublishedDate = value; }
            get { return this._PublishedDate; }
        }

        public DateTime ExpiredDate
        {
            set { this._ExpiredDate = value; }
            get { return this._ExpiredDate; }
        }


        #endregion

        //---------------------------------------------------------------------------------------------

        #region Select methods.

        public static Item Load(int id)
        {
            const string spName = "[dbo].[p_CMS_Item_Load]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, id);
            Item entity = null;
            IDataReader reader = db.ExecuteReader(dbCommand);
            if (reader.Read())
            {
                entity = new Item();
                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) entity.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("CategoryID"))) entity.CategoryID = reader.GetInt32(reader.GetOrdinal("CategoryID"));
                if (!reader.IsDBNull(reader.GetOrdinal("GroupID"))) entity.GroupID = reader.GetInt32(reader.GetOrdinal("GroupID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Title"))) entity.Title = reader.GetString(reader.GetOrdinal("Title"));
                if (!reader.IsDBNull(reader.GetOrdinal("SubTitle"))) entity.SubTitle = reader.GetString(reader.GetOrdinal("SubTitle"));
                if (!reader.IsDBNull(reader.GetOrdinal("Body"))) entity.Body = reader.GetString(reader.GetOrdinal("Body"));
                if (!reader.IsDBNull(reader.GetOrdinal("ImagePath"))) entity.ImagePath = reader.GetString(reader.GetOrdinal("ImagePath"));
                if (!reader.IsDBNull(reader.GetOrdinal("Authors"))) entity.Authors = reader.GetString(reader.GetOrdinal("Authors"));
                if (!reader.IsDBNull(reader.GetOrdinal("Rate"))) entity.Rate = reader.GetByte(reader.GetOrdinal("Rate"));
                if (!reader.IsDBNull(reader.GetOrdinal("Status"))) entity.Status = reader.GetString(reader.GetOrdinal("Status"));
                if (!reader.IsDBNull(reader.GetOrdinal("DisplayType"))) entity.DisplayType = reader.GetString(reader.GetOrdinal("DisplayType"));
                if (!reader.IsDBNull(reader.GetOrdinal("TotalViews"))) entity.TotalViews = reader.GetInt32(reader.GetOrdinal("TotalViews"));
                if (!reader.IsDBNull(reader.GetOrdinal("TotalComments"))) entity.TotalComments = reader.GetInt32(reader.GetOrdinal("TotalComments"));
                if (!reader.IsDBNull(reader.GetOrdinal("Keywords"))) entity.Keywords = reader.GetString(reader.GetOrdinal("Keywords"));
                if (!reader.IsDBNull(reader.GetOrdinal("CreatedBy"))) entity.CreatedBy = reader.GetString(reader.GetOrdinal("CreatedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("CreatedDate"))) entity.CreatedDate = reader.GetDateTime(reader.GetOrdinal("CreatedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("ApprovedBy"))) entity.ApprovedBy = reader.GetString(reader.GetOrdinal("ApprovedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("ApprovedDate"))) entity.ApprovedDate = reader.GetDateTime(reader.GetOrdinal("ApprovedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("PublishedDate"))) entity.PublishedDate = reader.GetDateTime(reader.GetOrdinal("PublishedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("ExpiredDate"))) entity.ExpiredDate = reader.GetDateTime(reader.GetOrdinal("ExpiredDate"));
            }
            reader.Close();
            return entity;
        }

        //---------------------------------------------------------------------------------------------
        public static List<Item> SelectCollectionAll()
        {
            List<Item> collection = new List<Item>();
            SqlDataReader reader = (SqlDataReader)SelectReaderAll();
            while (reader.Read())
            {
                Item entity = new Item();

                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) entity.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("CategoryID"))) entity.CategoryID = reader.GetInt32(reader.GetOrdinal("CategoryID"));
                if (!reader.IsDBNull(reader.GetOrdinal("GroupID"))) entity.GroupID = reader.GetInt32(reader.GetOrdinal("GroupID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Title"))) entity.Title = reader.GetString(reader.GetOrdinal("Title"));
                if (!reader.IsDBNull(reader.GetOrdinal("SubTitle"))) entity.SubTitle = reader.GetString(reader.GetOrdinal("SubTitle"));
                if (!reader.IsDBNull(reader.GetOrdinal("Body"))) entity.Body = reader.GetString(reader.GetOrdinal("Body"));
                if (!reader.IsDBNull(reader.GetOrdinal("ImagePath"))) entity.ImagePath = reader.GetString(reader.GetOrdinal("ImagePath"));
                if (!reader.IsDBNull(reader.GetOrdinal("Authors"))) entity.Authors = reader.GetString(reader.GetOrdinal("Authors"));
                if (!reader.IsDBNull(reader.GetOrdinal("Rate"))) entity.Rate = reader.GetByte(reader.GetOrdinal("Rate"));
                if (!reader.IsDBNull(reader.GetOrdinal("Status"))) entity.Status = reader.GetString(reader.GetOrdinal("Status"));
                if (!reader.IsDBNull(reader.GetOrdinal("DisplayType"))) entity.DisplayType = reader.GetString(reader.GetOrdinal("DisplayType"));
                if (!reader.IsDBNull(reader.GetOrdinal("TotalViews"))) entity.TotalViews = reader.GetInt32(reader.GetOrdinal("TotalViews"));
                if (!reader.IsDBNull(reader.GetOrdinal("TotalComments"))) entity.TotalComments = reader.GetInt32(reader.GetOrdinal("TotalComments"));
                if (!reader.IsDBNull(reader.GetOrdinal("Keywords"))) entity.Keywords = reader.GetString(reader.GetOrdinal("Keywords"));
                if (!reader.IsDBNull(reader.GetOrdinal("CreatedBy"))) entity.CreatedBy = reader.GetString(reader.GetOrdinal("CreatedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("CreatedDate"))) entity.CreatedDate = reader.GetDateTime(reader.GetOrdinal("CreatedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("ApprovedBy"))) entity.ApprovedBy = reader.GetString(reader.GetOrdinal("ApprovedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("ApprovedDate"))) entity.ApprovedDate = reader.GetDateTime(reader.GetOrdinal("ApprovedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("PublishedDate"))) entity.PublishedDate = reader.GetDateTime(reader.GetOrdinal("PublishedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("ExpiredDate"))) entity.ExpiredDate = reader.GetDateTime(reader.GetOrdinal("ExpiredDate"));
                collection.Add(entity);
            }

            reader.Close();
            return collection;
        }

        //---------------------------------------------------------------------------------------------

        public static List<Item> SelectCollectionDynamic(string whereCondition, string orderByExpression)
        {
            List<Item> collection = new List<Item>();

            SqlDataReader reader = (SqlDataReader)SelectReaderDynamic(whereCondition, orderByExpression);
            while (reader.Read())
            {
                Item entity = new Item();

                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) entity.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("CategoryID"))) entity.CategoryID = reader.GetInt32(reader.GetOrdinal("CategoryID"));
                if (!reader.IsDBNull(reader.GetOrdinal("GroupID"))) entity.GroupID = reader.GetInt32(reader.GetOrdinal("GroupID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Title"))) entity.Title = reader.GetString(reader.GetOrdinal("Title"));
                if (!reader.IsDBNull(reader.GetOrdinal("SubTitle"))) entity.SubTitle = reader.GetString(reader.GetOrdinal("SubTitle"));
                if (!reader.IsDBNull(reader.GetOrdinal("Body"))) entity.Body = reader.GetString(reader.GetOrdinal("Body"));
                if (!reader.IsDBNull(reader.GetOrdinal("ImagePath"))) entity.ImagePath = reader.GetString(reader.GetOrdinal("ImagePath"));
                if (!reader.IsDBNull(reader.GetOrdinal("Authors"))) entity.Authors = reader.GetString(reader.GetOrdinal("Authors"));
                if (!reader.IsDBNull(reader.GetOrdinal("Rate"))) entity.Rate = reader.GetByte(reader.GetOrdinal("Rate"));
                if (!reader.IsDBNull(reader.GetOrdinal("Status"))) entity.Status = reader.GetString(reader.GetOrdinal("Status"));
                if (!reader.IsDBNull(reader.GetOrdinal("DisplayType"))) entity.DisplayType = reader.GetString(reader.GetOrdinal("DisplayType"));
                if (!reader.IsDBNull(reader.GetOrdinal("TotalViews"))) entity.TotalViews = reader.GetInt32(reader.GetOrdinal("TotalViews"));
                if (!reader.IsDBNull(reader.GetOrdinal("TotalComments"))) entity.TotalComments = reader.GetInt32(reader.GetOrdinal("TotalComments"));
                if (!reader.IsDBNull(reader.GetOrdinal("Keywords"))) entity.Keywords = reader.GetString(reader.GetOrdinal("Keywords"));
                if (!reader.IsDBNull(reader.GetOrdinal("CreatedBy"))) entity.CreatedBy = reader.GetString(reader.GetOrdinal("CreatedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("CreatedDate"))) entity.CreatedDate = reader.GetDateTime(reader.GetOrdinal("CreatedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("ApprovedBy"))) entity.ApprovedBy = reader.GetString(reader.GetOrdinal("ApprovedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("ApprovedDate"))) entity.ApprovedDate = reader.GetDateTime(reader.GetOrdinal("ApprovedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("PublishedDate"))) entity.PublishedDate = reader.GetDateTime(reader.GetOrdinal("PublishedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("ExpiredDate"))) entity.ExpiredDate = reader.GetDateTime(reader.GetOrdinal("ExpiredDate"));
                collection.Add(entity);
            }

            reader.Close();
            return collection;
        }

        //---------------------------------------------------------------------------------------------

        // Select by foreign key return collection		
        public static IList<Item> SelectCollectionBy_CategoryID(int categoryID)
        {
            List<Item> collection = new List<Item>();
            SqlDataReader reader = (SqlDataReader)SelectReaderBy_CategoryID(categoryID);
            while (reader.Read())
            {
                Item entity = new Item();
                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) entity.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("CategoryID"))) entity.CategoryID = reader.GetInt32(reader.GetOrdinal("CategoryID"));
                if (!reader.IsDBNull(reader.GetOrdinal("GroupID"))) entity.GroupID = reader.GetInt32(reader.GetOrdinal("GroupID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Title"))) entity.Title = reader.GetString(reader.GetOrdinal("Title"));
                if (!reader.IsDBNull(reader.GetOrdinal("SubTitle"))) entity.SubTitle = reader.GetString(reader.GetOrdinal("SubTitle"));
                if (!reader.IsDBNull(reader.GetOrdinal("Body"))) entity.Body = reader.GetString(reader.GetOrdinal("Body"));
                if (!reader.IsDBNull(reader.GetOrdinal("ImagePath"))) entity.ImagePath = reader.GetString(reader.GetOrdinal("ImagePath"));
                if (!reader.IsDBNull(reader.GetOrdinal("Authors"))) entity.Authors = reader.GetString(reader.GetOrdinal("Authors"));
                if (!reader.IsDBNull(reader.GetOrdinal("Rate"))) entity.Rate = reader.GetByte(reader.GetOrdinal("Rate"));
                if (!reader.IsDBNull(reader.GetOrdinal("Status"))) entity.Status = reader.GetString(reader.GetOrdinal("Status"));
                if (!reader.IsDBNull(reader.GetOrdinal("DisplayType"))) entity.DisplayType = reader.GetString(reader.GetOrdinal("DisplayType"));
                if (!reader.IsDBNull(reader.GetOrdinal("TotalViews"))) entity.TotalViews = reader.GetInt32(reader.GetOrdinal("TotalViews"));
                if (!reader.IsDBNull(reader.GetOrdinal("TotalComments"))) entity.TotalComments = reader.GetInt32(reader.GetOrdinal("TotalComments"));
                if (!reader.IsDBNull(reader.GetOrdinal("Keywords"))) entity.Keywords = reader.GetString(reader.GetOrdinal("Keywords"));
                if (!reader.IsDBNull(reader.GetOrdinal("CreatedBy"))) entity.CreatedBy = reader.GetString(reader.GetOrdinal("CreatedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("CreatedDate"))) entity.CreatedDate = reader.GetDateTime(reader.GetOrdinal("CreatedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("ApprovedBy"))) entity.ApprovedBy = reader.GetString(reader.GetOrdinal("ApprovedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("ApprovedDate"))) entity.ApprovedDate = reader.GetDateTime(reader.GetOrdinal("ApprovedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("PublishedDate"))) entity.PublishedDate = reader.GetDateTime(reader.GetOrdinal("PublishedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("ExpiredDate"))) entity.ExpiredDate = reader.GetDateTime(reader.GetOrdinal("ExpiredDate"));
                collection.Add(entity);
            }
            reader.Close();
            return collection;
        }
        //---------------------------------------------------------------------------------------------
        public static IList<Item> SelectCollectionBy_GroupID(int groupID)
        {
            List<Item> collection = new List<Item>();
            SqlDataReader reader = (SqlDataReader)SelectReaderBy_GroupID(groupID);
            while (reader.Read())
            {
                Item entity = new Item();
                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) entity.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("CategoryID"))) entity.CategoryID = reader.GetInt32(reader.GetOrdinal("CategoryID"));
                if (!reader.IsDBNull(reader.GetOrdinal("GroupID"))) entity.GroupID = reader.GetInt32(reader.GetOrdinal("GroupID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Title"))) entity.Title = reader.GetString(reader.GetOrdinal("Title"));
                if (!reader.IsDBNull(reader.GetOrdinal("SubTitle"))) entity.SubTitle = reader.GetString(reader.GetOrdinal("SubTitle"));
                if (!reader.IsDBNull(reader.GetOrdinal("Body"))) entity.Body = reader.GetString(reader.GetOrdinal("Body"));
                if (!reader.IsDBNull(reader.GetOrdinal("ImagePath"))) entity.ImagePath = reader.GetString(reader.GetOrdinal("ImagePath"));
                if (!reader.IsDBNull(reader.GetOrdinal("Authors"))) entity.Authors = reader.GetString(reader.GetOrdinal("Authors"));
                if (!reader.IsDBNull(reader.GetOrdinal("Rate"))) entity.Rate = reader.GetByte(reader.GetOrdinal("Rate"));
                if (!reader.IsDBNull(reader.GetOrdinal("Status"))) entity.Status = reader.GetString(reader.GetOrdinal("Status"));
                if (!reader.IsDBNull(reader.GetOrdinal("DisplayType"))) entity.DisplayType = reader.GetString(reader.GetOrdinal("DisplayType"));
                if (!reader.IsDBNull(reader.GetOrdinal("TotalViews"))) entity.TotalViews = reader.GetInt32(reader.GetOrdinal("TotalViews"));
                if (!reader.IsDBNull(reader.GetOrdinal("TotalComments"))) entity.TotalComments = reader.GetInt32(reader.GetOrdinal("TotalComments"));
                if (!reader.IsDBNull(reader.GetOrdinal("Keywords"))) entity.Keywords = reader.GetString(reader.GetOrdinal("Keywords"));
                if (!reader.IsDBNull(reader.GetOrdinal("CreatedBy"))) entity.CreatedBy = reader.GetString(reader.GetOrdinal("CreatedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("CreatedDate"))) entity.CreatedDate = reader.GetDateTime(reader.GetOrdinal("CreatedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("ApprovedBy"))) entity.ApprovedBy = reader.GetString(reader.GetOrdinal("ApprovedBy"));
                if (!reader.IsDBNull(reader.GetOrdinal("ApprovedDate"))) entity.ApprovedDate = reader.GetDateTime(reader.GetOrdinal("ApprovedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("PublishedDate"))) entity.PublishedDate = reader.GetDateTime(reader.GetOrdinal("PublishedDate"));
                if (!reader.IsDBNull(reader.GetOrdinal("ExpiredDate"))) entity.ExpiredDate = reader.GetDateTime(reader.GetOrdinal("ExpiredDate"));
                collection.Add(entity);
            }
            reader.Close();
            return collection;
        }
        //---------------------------------------------------------------------------------------------

        public static DataSet SelectBy_CategoryID(int categoryID)
        {
            const string spName = "[dbo].[p_CMS_Item_SelectBy_CategoryID]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@CategoryID", SqlDbType.Int, categoryID);

            return db.ExecuteDataSet(dbCommand);
        }
        //---------------------------------------------------------------------------------------------
        public static DataSet SelectBy_GroupID(int groupID)
        {
            const string spName = "[dbo].[p_CMS_Item_SelectBy_GroupID]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@GroupID", SqlDbType.Int, groupID);

            return db.ExecuteDataSet(dbCommand);
        }
        //---------------------------------------------------------------------------------------------

        public static DataSet SelectAll()
        {
            const string spName = "[dbo].[p_CMS_Item_SelectAll]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);


            return db.ExecuteDataSet(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static DataSet SelectDynamic(string whereCondition, string orderByExpression)
        {
            const string spName = "[dbo].[p_CMS_Item_SelectDynamic]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);

            return db.ExecuteDataSet(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static IDataReader SelectReaderAll()
        {
            const string spName = "[dbo].[p_CMS_Item_SelectAll]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            return db.ExecuteReader(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static IDataReader SelectReaderDynamic(string whereCondition, string orderByExpression)
        {
            const string spName = "[dbo].[p_CMS_Item_SelectDynamic]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);

            return db.ExecuteReader(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        // Select by foreign key return collection		
        public static IDataReader SelectReaderBy_CategoryID(int categoryID)
        {
            const string spName = "p_CMS_Item_SelectBy_CategoryID";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@CategoryID", SqlDbType.Int, categoryID);

            return db.ExecuteReader(dbCommand);
        }
        //---------------------------------------------------------------------------------------------
        public static IDataReader SelectReaderBy_GroupID(int groupID)
        {
            const string spName = "p_CMS_Item_SelectBy_GroupID";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@GroupID", SqlDbType.Int, groupID);

            return db.ExecuteReader(dbCommand);
        }
        //---------------------------------------------------------------------------------------------

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Insert methods.

        public static int InsertItem(int categoryID, int groupID, string title, string subTitle, string body, string imagePath, string authors, byte rate, string status, string displayType, int totalViews, int totalComments, string keywords, string createdBy, DateTime createdDate, string approvedBy, DateTime approvedDate, DateTime publishedDate, DateTime expiredDate)
        {
            Item entity = new Item();
            entity.CategoryID = categoryID;
            entity.GroupID = groupID;
            entity.Title = title;
            entity.SubTitle = subTitle;
            entity.Body = body;
            entity.ImagePath = imagePath;
            entity.Authors = authors;
            entity.Rate = rate;
            entity.Status = status;
            entity.DisplayType = displayType;
            entity.TotalViews = totalViews;
            entity.TotalComments = totalComments;
            entity.Keywords = keywords;
            entity.CreatedBy = createdBy;
            entity.CreatedDate = createdDate;
            entity.ApprovedBy = approvedBy;
            entity.ApprovedDate = approvedDate;
            entity.PublishedDate = publishedDate;
            entity.ExpiredDate = expiredDate;
            return entity.Insert();
        }

        public int Insert()
        {
            return this.Insert(null);
        }

        //---------------------------------------------------------------------------------------------

        public int Insert(SqlTransaction transaction)
        {
            const string spName = "[dbo].[p_CMS_Item_Insert]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddOutParameter(dbCommand, "@ID", SqlDbType.Int, 4);
            db.AddInParameter(dbCommand, "@CategoryID", SqlDbType.Int, CategoryID);
            db.AddInParameter(dbCommand, "@GroupID", SqlDbType.Int, GroupID);
            db.AddInParameter(dbCommand, "@Title", SqlDbType.NVarChar, Title);
            db.AddInParameter(dbCommand, "@SubTitle", SqlDbType.NVarChar, SubTitle);
            db.AddInParameter(dbCommand, "@Body", SqlDbType.NVarChar, Body);
            db.AddInParameter(dbCommand, "@ImagePath", SqlDbType.NVarChar, ImagePath);
            db.AddInParameter(dbCommand, "@Authors", SqlDbType.NVarChar, Authors);
            db.AddInParameter(dbCommand, "@Rate", SqlDbType.TinyInt, Rate);
            db.AddInParameter(dbCommand, "@Status", SqlDbType.VarChar, Status);
            db.AddInParameter(dbCommand, "@DisplayType", SqlDbType.VarChar, DisplayType);
            db.AddInParameter(dbCommand, "@TotalViews", SqlDbType.Int, TotalViews);
            db.AddInParameter(dbCommand, "@TotalComments", SqlDbType.Int, TotalComments);
            db.AddInParameter(dbCommand, "@Keywords", SqlDbType.NVarChar, Keywords);
            db.AddInParameter(dbCommand, "@CreatedBy", SqlDbType.VarChar, CreatedBy);
            db.AddInParameter(dbCommand, "@CreatedDate", SqlDbType.DateTime, CreatedDate.Year <= 1753 ? DBNull.Value : (object)CreatedDate);
            db.AddInParameter(dbCommand, "@ApprovedBy", SqlDbType.VarChar, ApprovedBy);
            db.AddInParameter(dbCommand, "@ApprovedDate", SqlDbType.DateTime, ApprovedDate.Year <= 1753 ? DBNull.Value : (object)ApprovedDate);
            db.AddInParameter(dbCommand, "@PublishedDate", SqlDbType.DateTime, PublishedDate.Year <= 1753 ? DBNull.Value : (object)PublishedDate);
            db.AddInParameter(dbCommand, "@ExpiredDate", SqlDbType.DateTime, ExpiredDate.Year <= 1753 ? DBNull.Value : (object)ExpiredDate);

            if (transaction != null)
            {
                db.ExecuteNonQuery(dbCommand, transaction);
                ID = (int)db.GetParameterValue(dbCommand, "@ID");
                return ID;
            }
            else
            {
                db.ExecuteNonQuery(dbCommand);
                ID = (int)db.GetParameterValue(dbCommand, "@ID");
                return ID;
            }
        }

        //---------------------------------------------------------------------------------------------
        public static bool InsertCollection(IList<Item> collection)
        {
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        bool ret01 = true;
                        foreach (Item item in collection)
                        {
                            if (item.Insert(transaction) <= 0)
                            {
                                ret01 = false;
                                break;
                            }
                        }
                        if (ret01)
                        {
                            transaction.Commit();
                            ret = true;
                        }
                        else
                        {
                            transaction.Rollback();
                            ret = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception("Error at InsertCollection method: " + ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return ret;
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Insert / Update methods.

        public static int InsertUpdateItem(int id, int categoryID, int groupID, string title, string subTitle, string body, string imagePath, string authors, byte rate, string status, string displayType, int totalViews, int totalComments, string keywords, string createdBy, DateTime createdDate, string approvedBy, DateTime approvedDate, DateTime publishedDate, DateTime expiredDate)
        {
            Item entity = new Item();
            entity.ID = id;
            entity.CategoryID = categoryID;
            entity.GroupID = groupID;
            entity.Title = title;
            entity.SubTitle = subTitle;
            entity.Body = body;
            entity.ImagePath = imagePath;
            entity.Authors = authors;
            entity.Rate = rate;
            entity.Status = status;
            entity.DisplayType = displayType;
            entity.TotalViews = totalViews;
            entity.TotalComments = totalComments;
            entity.Keywords = keywords;
            entity.CreatedBy = createdBy;
            entity.CreatedDate = createdDate;
            entity.ApprovedBy = approvedBy;
            entity.ApprovedDate = approvedDate;
            entity.PublishedDate = publishedDate;
            entity.ExpiredDate = expiredDate;
            return entity.InsertUpdate();
        }

        //---------------------------------------------------------------------------------------------

        public int InsertUpdate()
        {
            return this.InsertUpdate(null);
        }

        //---------------------------------------------------------------------------------------------

        public int InsertUpdate(SqlTransaction transaction)
        {
            const string spName = "p_CMS_Item_InsertUpdate";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, ID);
            db.AddInParameter(dbCommand, "@CategoryID", SqlDbType.Int, CategoryID);
            db.AddInParameter(dbCommand, "@GroupID", SqlDbType.Int, GroupID);
            db.AddInParameter(dbCommand, "@Title", SqlDbType.NVarChar, Title);
            db.AddInParameter(dbCommand, "@SubTitle", SqlDbType.NVarChar, SubTitle);
            db.AddInParameter(dbCommand, "@Body", SqlDbType.NVarChar, Body);
            db.AddInParameter(dbCommand, "@ImagePath", SqlDbType.NVarChar, ImagePath);
            db.AddInParameter(dbCommand, "@Authors", SqlDbType.NVarChar, Authors);
            db.AddInParameter(dbCommand, "@Rate", SqlDbType.TinyInt, Rate);
            db.AddInParameter(dbCommand, "@Status", SqlDbType.VarChar, Status);
            db.AddInParameter(dbCommand, "@DisplayType", SqlDbType.VarChar, DisplayType);
            db.AddInParameter(dbCommand, "@TotalViews", SqlDbType.Int, TotalViews);
            db.AddInParameter(dbCommand, "@TotalComments", SqlDbType.Int, TotalComments);
            db.AddInParameter(dbCommand, "@Keywords", SqlDbType.NVarChar, Keywords);
            db.AddInParameter(dbCommand, "@CreatedBy", SqlDbType.VarChar, CreatedBy);
            db.AddInParameter(dbCommand, "@CreatedDate", SqlDbType.DateTime, CreatedDate.Year == 1753 ? DBNull.Value : (object)CreatedDate);
            db.AddInParameter(dbCommand, "@ApprovedBy", SqlDbType.VarChar, ApprovedBy);
            db.AddInParameter(dbCommand, "@ApprovedDate", SqlDbType.DateTime, ApprovedDate.Year == 1753 ? DBNull.Value : (object)ApprovedDate);
            db.AddInParameter(dbCommand, "@PublishedDate", SqlDbType.DateTime, PublishedDate.Year == 1753 ? DBNull.Value : (object)PublishedDate);
            db.AddInParameter(dbCommand, "@ExpiredDate", SqlDbType.DateTime, ExpiredDate.Year == 1753 ? DBNull.Value : (object)ExpiredDate);

            if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------
        public static bool InsertUpdateCollection(IList<Item> collection)
        {
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        bool ret01 = true;
                        foreach (Item item in collection)
                        {
                            if (item.InsertUpdate(transaction) <= 0)
                            {
                                ret01 = false;
                                break;
                            }
                        }
                        if (ret01)
                        {
                            transaction.Commit();
                            ret = true;
                        }
                        else
                        {
                            transaction.Rollback();
                            ret = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception("Error at InsertUpdateCollection method: " + ex.Message);

                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return ret;
        }
        #endregion

        //---------------------------------------------------------------------------------------------

        #region Update methods.

        public static int UpdateItem(int id, int categoryID, int groupID, string title, string subTitle, string body, string imagePath, string authors, byte rate, string status, string displayType, int totalViews, int totalComments, string keywords, string createdBy, DateTime createdDate, string approvedBy, DateTime approvedDate, DateTime publishedDate, DateTime expiredDate)
        {
            Item entity = new Item();
            entity.ID = id;
            entity.CategoryID = categoryID;
            entity.GroupID = groupID;
            entity.Title = title;
            entity.SubTitle = subTitle;
            entity.Body = body;
            entity.ImagePath = imagePath;
            entity.Authors = authors;
            entity.Rate = rate;
            entity.Status = status;
            entity.DisplayType = displayType;
            entity.TotalViews = totalViews;
            entity.TotalComments = totalComments;
            entity.Keywords = keywords;
            entity.CreatedBy = createdBy;
            entity.CreatedDate = createdDate;
            entity.ApprovedBy = approvedBy;
            entity.ApprovedDate = approvedDate;
            entity.PublishedDate = publishedDate;
            entity.ExpiredDate = expiredDate;
            return entity.Update();
        }

        //---------------------------------------------------------------------------------------------

        public int Update()
        {
            return this.Update(null);
        }

        //---------------------------------------------------------------------------------------------

        public int Update(SqlTransaction transaction)
        {
            const string spName = "[dbo].[p_CMS_Item_Update]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, ID);
            db.AddInParameter(dbCommand, "@CategoryID", SqlDbType.Int, CategoryID);
            db.AddInParameter(dbCommand, "@GroupID", SqlDbType.Int, GroupID);
            db.AddInParameter(dbCommand, "@Title", SqlDbType.NVarChar, Title);
            db.AddInParameter(dbCommand, "@SubTitle", SqlDbType.NVarChar, SubTitle);
            db.AddInParameter(dbCommand, "@Body", SqlDbType.NVarChar, Body);
            db.AddInParameter(dbCommand, "@ImagePath", SqlDbType.NVarChar, ImagePath);
            db.AddInParameter(dbCommand, "@Authors", SqlDbType.NVarChar, Authors);
            db.AddInParameter(dbCommand, "@Rate", SqlDbType.TinyInt, Rate);
            db.AddInParameter(dbCommand, "@Status", SqlDbType.VarChar, Status);
            db.AddInParameter(dbCommand, "@DisplayType", SqlDbType.VarChar, DisplayType);
            db.AddInParameter(dbCommand, "@TotalViews", SqlDbType.Int, TotalViews);
            db.AddInParameter(dbCommand, "@TotalComments", SqlDbType.Int, TotalComments);
            db.AddInParameter(dbCommand, "@Keywords", SqlDbType.NVarChar, Keywords);
            db.AddInParameter(dbCommand, "@CreatedBy", SqlDbType.VarChar, CreatedBy);
            db.AddInParameter(dbCommand, "@CreatedDate", SqlDbType.DateTime, CreatedDate.Year == 1753 ? DBNull.Value : (object)CreatedDate);
            db.AddInParameter(dbCommand, "@ApprovedBy", SqlDbType.VarChar, ApprovedBy);
            db.AddInParameter(dbCommand, "@ApprovedDate", SqlDbType.DateTime, ApprovedDate.Year == 1753 ? DBNull.Value : (object)ApprovedDate);
            db.AddInParameter(dbCommand, "@PublishedDate", SqlDbType.DateTime, PublishedDate.Year == 1753 ? DBNull.Value : (object)PublishedDate);
            db.AddInParameter(dbCommand, "@ExpiredDate", SqlDbType.DateTime, ExpiredDate.Year == 1753 ? DBNull.Value : (object)ExpiredDate);

            if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------
        public static bool UpdateCollection(IList<Item> collection)
        {
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        bool ret01 = true;
                        foreach (Item item in collection)
                        {
                            if (item.Update(transaction) <= 0)
                            {
                                ret01 = false;
                                break;
                            }
                        }
                        if (ret01)
                        {
                            transaction.Commit();
                            ret = true;
                        }
                        else
                        {
                            transaction.Rollback();
                            ret = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception("Error at UpdateCollection method: " + ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return ret;
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Delete methods.

        public static int DeleteItem(int id)
        {
            Item entity = new Item();
            entity.ID = id;

            return entity.Delete();
        }

        public int Delete()
        {
            return this.Delete(null);
        }

        //---------------------------------------------------------------------------------------------

        public int Delete(SqlTransaction transaction)
        {
            const string spName = "[dbo].[p_CMS_Item_Delete]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, ID);

            if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static int DeleteBy_CategoryID(int categoryID)
        {
            string spName = "[dbo].[p_CMS_Item_DeleteBy_CategoryID]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@CategoryID", SqlDbType.Int, categoryID);

            return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static int DeleteBy_GroupID(int groupID)
        {
            string spName = "[dbo].[p_CMS_Item_DeleteBy_GroupID]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@GroupID", SqlDbType.Int, groupID);

            return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static bool DeleteCollection(IList<Item> collection)
        {
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        bool ret01 = true;
                        foreach (Item item in collection)
                        {
                            if (item.Delete(transaction) <= 0)
                            {
                                ret01 = false;
                                break;
                            }
                        }
                        if (ret01)
                        {
                            transaction.Commit();
                            ret = true;
                        }
                        else
                        {
                            transaction.Rollback();
                            ret = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception("Error at DeleteCollection method: " + ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return ret;
        }
        #endregion
    }
}